#region Using directives
using System;
using System.Data ;
using WP.DB;
using WP.MF.DataEntity;
using WP.Commons;
#endregion
namespace WP.MF.DataAccess
{

	/*
	 * Created by SmartCreateCode Tool
	 * At 4/22/2005
	 */

    public class MSalesTaxDAL : IDataAccess
	{
		public MSalesTaxDAL()
		{
		}

        #region Select List
        public string SQLList(int iParameter)
        {
            string strSQL = @"SELECT RecID,TaxCode,Description,TaxPct,StartDate,EndDate,ProvNo,ProvTaxPct,CountyNo,CountyTaxPct,CityNo,CityTaxPct FROM mSalesTax order by TaxCode ";
            return strSQL;
        }
        #endregion

        #region Fields
        public string Fields
        {
            get
            {
                return @"TaxCode,Description,TaxPct,ProvNo,ProvTaxPct,CountyNo,CountyTaxPct,CityNo,CityTaxPct,
				CodeforTaxLevel4,PctforTaxLevel4,CodeforTaxLevel5,PctforTaxLevel5,CodeforTaxLevel6,
				PctforTaxLevel6,ReversionNo,StartDate,EndDate,TaxOnFrt,PayableUsePST,TaxShortDesc";
            }
        }
        #endregion

        #region Select by RecID
        public MSalesTaxDEL SelectDataByRecID(DBAccess dBAccess, string strRecId)
        {
            MSalesTaxDEL mSalesTaxDEL = new MSalesTaxDEL();

            DataFunctions.SelectByRecID(dBAccess, mSalesTaxDEL, Fields, strRecId);

            return mSalesTaxDEL;
        }

		#endregion

        #region Update
        public bool UpdateMSalesTax(DBAccess dBAccess, MSalesTaxDEL mSalesTaxDEL)
        {
            bool bUpdateTable = false;
            string strSql = string.Empty;

            DataRow drSaleTax = mSalesTaxDEL.Tables[0].Rows[0];
            string strReversionNo = drSaleTax["ReversionNo"].ToString();
            string strTaxCode = drSaleTax["TaxCode"].ToString();

            DataFunctions.UpdateTable(dBAccess, Fields, MSalesTaxDEL.TABLENAME_MSALESTAX, mSalesTaxDEL.Tables[0].Rows[0]);

            if (!UpdateMSalesTaxDtlInfo(dBAccess, mSalesTaxDEL))
                return bUpdateTable;

            return true;
        }

        public bool InsertMSalesTax(DBAccess dBAccess, MSalesTaxDEL mSalesTaxDEL)
        {
            bool bInsertTable = false;
            string strSql = string.Empty;

            DataFunctions.InsertTable(dBAccess, Fields, mSalesTaxDEL);

            if (!UpdateMSalesTaxDtlInfo(dBAccess, mSalesTaxDEL))
                return bInsertTable;
            
            return true;
        }

        private bool UpdateMSalesTaxDtlInfo(DBAccess dBAccess, MSalesTaxDEL mSalesTaxDEL)
        {
            bool bUpdateDtl = false;
            string strSql = string.Empty;
            DataRow drSaleTax = mSalesTaxDEL.Tables[0].Rows[0];
            string strReversionNo = drSaleTax["ReversionNo"].ToString();
            string strTaxCode = drSaleTax["TaxCode"].ToString();

            if (!UpdateCountyAndCity(dBAccess, mSalesTaxDEL))
                return bUpdateDtl;

            // update detail
            strSql = "delete from mSalestaxDtl where reversionno=" + BaseFunctions.FormatNumeric(strReversionNo, "n.0") +
                    " and Taxcode='" + strTaxCode + "'";
            dBAccess.ExecuteSql(strSql);

            strSql = "insert into mSalestaxDtl (ReversionNo,TaxCode,CodeTaxLevel,SubItem,TaxPct,TaxLevel) " +
                    " select ReversionNo,TaxCode,CodeTaxLevel,SubItem,TaxPct,TaxLevel from #mSalestaxDtl " +
                    " where reversionno=" + BaseFunctions.FormatNumeric(strReversionNo, "n.0");
            dBAccess.ExecuteSql(strSql);

            if (!UpdateInValidDtl(dBAccess))
                return bUpdateDtl;

            return true;
        }

        private bool UpdateCountyAndCity(DBAccess dBAccess, MSalesTaxDEL mSalesTaxDEL)
        {
            bool bUpdateCountyAndCity = false;
            string strSql = string.Empty;
            DataTable dtTable = null;
            DataRow drTable = null;
            DataRow drSaleTax = mSalesTaxDEL.Tables[0].Rows[0];
            string strCountyDesc = string.Empty;
            string strCityDesc = string.Empty;
            string strProvNo = drSaleTax["ProvNo"].ToString().TrimEnd();
            string strCountyNo = drSaleTax["CountyNo"].ToString().TrimEnd();
            string strCityNo = drSaleTax["CityNo"].ToString().TrimEnd();
            string strCountyTaxPct = drSaleTax["CountyTaxPct"].ToString();
            string strCityTaxPct = drSaleTax["CityTaxPct"].ToString();
            FormStatus fsStatus = FormStatus.Update;

            #region Update mSalesTaxCounty
            string strCode = strProvNo + strCountyNo;
            if (strCode.Length > 7) { strCode = strCode.Substring(0, 7); }
            strSql = "select * from mSalesTaxCounty where Code='" + strCode + "'";
            //strSql = "select * from mSalesTaxCounty where CountyNo='" + strCountyNo + "' and ProvNo='" + strProvNo + "'";
            dtTable = dBAccess.GetDataTable(strSql);
            if (dtTable.Rows.Count == 0)
            {
                drTable = dtTable.NewRow();
                fsStatus = FormStatus.AddNew;
            }
            else
                drTable = dtTable.Rows[0];

            drTable["code"] = strCode;
            strCountyDesc = DataFunctions.GetFieldValue(dBAccess, "mSalesTaxCounty", "Description", "code='" + strProvNo + strCountyNo + "'");
            drTable["Description"] = strCountyDesc;
            drTable["CountyNo"] = strCountyNo;
            drTable["provno"] = strProvNo;
            drTable["TaxPct"] = BaseFunctions.FormatNumeric(strCountyTaxPct, "n.4");
            DataFunctions.UpdateTable(dBAccess, "code,Description,CountyNo,provno,TaxPct", "mSalesTaxCounty", drTable, fsStatus, "RecId", false);
            #endregion

            #region Update mSalesTaxCity
            strCode = strProvNo + strCountyNo + strCityNo;
            if (strCode.Length > 10) { strCode = strCode.Substring(0, 10); }
            strSql = "select * from mSalesTaxCity where Code='" + strCode + "'";
            //strSql = "select * from mSalesTaxCity where CityNo='" + strCityNo + "' and CountyNo='" + strCountyNo + "' and ProvNo='" + strProvNo + "'";
            dtTable = dBAccess.GetDataTable(strSql);
            if (dtTable.Rows.Count == 0)
            {
                drTable = dtTable.NewRow();
                fsStatus = FormStatus.AddNew;
            }
            else
                drTable = dtTable.Rows[0];

            drTable["code"] = strCode;
            strCityDesc = DataFunctions.GetFieldValue("mSalesTaxCity", "Description", "code='" + strProvNo + strCountyNo + strCityNo + "'");
            drTable["Description"] = strCityDesc;
            drTable["CityNo"] = strCityNo;
            drTable["CountyNo"] = strCountyNo;
            drTable["provno"] = strProvNo;
            drTable["TaxPct"] = BaseFunctions.FormatNumeric(strCityTaxPct, "n.4");
            DataFunctions.UpdateTable(dBAccess, "code,Description,CityNo,CountyNo,provno,TaxPct", "mSalesTaxCity", drTable, fsStatus, "RecId", false);
            #endregion

            bUpdateCountyAndCity = true;
            return bUpdateCountyAndCity;
        }

        private bool UpdateInValidDtl(DBAccess dBAccess)
        {
            bool bUpdateInValidDtl = false;
            string strSql = string.Empty;

            strSql = "delete from mSalestaxDtl where ReversionNo not in (select ReversionNo from msalesTax) " + 
                    " or TaxCode not in (select TaxCode from msalesTax)";
            dBAccess.ExecuteSql(strSql);

            bUpdateInValidDtl = true;
            return bUpdateInValidDtl;
        }

        #endregion

        #region deal with dtl
        public void InitDtlTmpTable(DBAccess dBAccess, string strRecID)
        {
            string strSql = string.Empty;
            DataRow drTax = null;

            DataFunctions.DelTemporyTable(dBAccess, "#mSalestaxDtl");
            strSql = "CREATE TABLE [dbo].[#mSalestaxDtl] (" +
                    "   [RecID] [int] IDENTITY (1, 1) NOT NULL ," +
                     "   [ReversionNo] [int] NOT NULL ," +
                     "   [TaxCode] [char] (10) NOT NULL ," +
                     "   [CodeTaxLevel] [char] (10) NOT NULL ," +
                     "   [SubItem] [char] (30) NOT NULL ," +
                     "   [TaxPct] [decimal](18, 4) NOT NULL ," +
                     "   [RecStamp] [timestamp] NOT NULL ," +
                     "   [TaxLevel]  [Int] not NULL DEFAULT (1)" +
                     ") ON [PRIMARY]";
            dBAccess.ExecuteSql(strSql);
            if (CvtFunc.ToInt(BaseFunctions.FormatNumeric(strRecID, "n.0")) > 0)
            {
                strSql = "select * from msalestax where recid=" + strRecID;
                drTax = dBAccess.GetDataFirstRow(strSql);
                if (drTax != null)
                {
                    strSql = "insert into #mSalestaxDtl (ReversionNo,TaxCode,CodeTaxLevel,SubItem,TaxPct,TaxLevel) " +
                           " select ReversionNo,TaxCode,CodeTaxLevel,SubItem,TaxPct,TaxLevel from mSalestaxDtl " +
                           " where reversionno=" + drTax["ReversionNo"].ToString() + " and Taxcode='" + drTax["TaxCode"].ToString() + "'";
                    dBAccess.ExecuteSql(strSql);
                }
            }
        }

        public string RefDtl(DBAccess dBAccess, int iTaxLevel, string strReversionNo, string[] strArrCode)
        {
            string strPct = string.Empty;
            string strCon = string.Empty;
            string strSql = string.Empty;
            DataRow drTaxDtl = null;
            string strProvNo = string.Empty;
            string strCountyNo = string.Empty;
            string strCityNo = string.Empty;
            string strTaxLevel4No = string.Empty;
            string strTaxLevel5No = string.Empty;
            string strTaxLevel6No = string.Empty;
            bool bTaxLevel1NeedDtl = false;
            bool bTaxLevel2NeedDtl = false;
            bool bTaxLevel3NeedDtl = false;
            bool bTaxLevel4NeedDtl = false;
            bool bTaxLevel5NeedDtl = false;
            string strTaxCode = string.Empty;


            for (int i = 0; i < strArrCode.Length; i++)
            {
                if (i == 0)
                    strProvNo = strArrCode[i];
                else if (i == 1)
                    strCountyNo = strArrCode[i];
                else if (i == 2)
                    strCityNo = strArrCode[i];
                else if (i == 3)
                    strTaxLevel4No = strArrCode[i];
                else if (i == 4)
                    strTaxLevel5No = strArrCode[i];
                else if (i == 5)
                    strTaxLevel6No = strArrCode[i];
            }

            bTaxLevel1NeedDtl = DataFunctions.GetmSwitchValue(dBAccess, "TaxLeveL1NeedDtl", "M");
            bTaxLevel2NeedDtl = DataFunctions.GetmSwitchValue(dBAccess, "TaxLeveL2NeedDtl", "M");
            bTaxLevel3NeedDtl = DataFunctions.GetmSwitchValue(dBAccess, "TaxLeveL3NeedDtl", "M");
            bTaxLevel4NeedDtl = DataFunctions.GetmSwitchValue(dBAccess, "TaxLeveL4NeedDtl", "M");
            bTaxLevel5NeedDtl = DataFunctions.GetmSwitchValue(dBAccess, "TaxLeveL5NeedDtl", "M");
            strTaxCode = strProvNo.TrimEnd() + strCountyNo + strCityNo;// +"'";

            strReversionNo = BaseFunctions.FormatNumeric(strReversionNo, "n.0");
            switch (iTaxLevel)//UCase(Trim(GetFieldnameFromIndex(Index, idxDataFields, gintFromFieldindex)))
            {
                case 1:
                    strCon = "ReversionNo=" + strReversionNo;
                    strCon+= " and taxcode in (select top 1  Taxcode from msalesTax where provno='" + strProvNo.TrimEnd() + "' order by RecID)";

                    RefreshTmpDtl(dBAccess, strProvNo, 1, strCon, strTaxCode);
                    if (bTaxLevel1NeedDtl)
                        strSql = "select isnull(sum(TaxPct),0) as taxpct from #msalesTaxDtl where TaxLevel=1";
                    else
                        strSql = "select ProvTaxPct as TaxPct from msalesTax where ProvNo='" + strProvNo + "' and ReversionNo=" + strReversionNo;

                    break;
                case 2:
                    if (bTaxLevel1NeedDtl)
                        strCon = " Taxcode in (select TaxCode from msalestaxdtl where ReversionNo=" + strReversionNo + " and CodeTaxLevel='" + strProvNo + "' and TaxLevel=1)";
                    else
                        strCon = " Taxcode in (select TaxCode from msalestax where ReversionNo=" + strReversionNo + " and Provno='" + strProvNo + "')";

                    strCon+= " and taxcode in (select top 1  Taxcode from msalestax where provno='" + strProvNo.TrimEnd() + "'  and CountyNo='" + strCountyNo + "' order by RecID)";

                    RefreshTmpDtl(dBAccess, strCountyNo, 2, strCon, strTaxCode);
                    if (bTaxLevel2NeedDtl)
                        strSql = "select isnull(sum(TaxPct),0) as taxpct from #msalesTaxDtl where TaxLevel=2";
                    else
                        strSql = "select CountyTaxPct as TaxPct from msalesTax where ProvNo='" + strProvNo.TrimEnd() + "' and CountyNo='" + strCountyNo + "' and ReversionNo=" + strReversionNo;

                    break;
                case 3:
                    if (bTaxLevel1NeedDtl)
                        strCon = " Taxcode in (select TaxCode from msalestaxdtl where ReversionNo=" + strReversionNo + " and CodeTaxLevel='" + strProvNo + "' and TaxLevel=1)";
                    else
                        strCon = " Taxcode in (select TaxCode from msalestax where ReversionNo=" + strReversionNo + " and Provno='" + strProvNo + "')";

                    strCon+= " and taxcode in (select top 1  Taxcode from msalestax where provno='" + strProvNo.TrimEnd() + "'  and CountyNo='" + strCountyNo + "' and cityNo='" + strCityNo + "' order by RecID)";
                    
                    if (bTaxLevel2NeedDtl)
                        strCon+= " and Taxcode in (select TaxCode from msalestaxdtl where ReversionNo=" + strReversionNo + " and CodeTaxLevel='" + strCountyNo + "' and TaxLevel=2)";
                    else
                        strCon+= " and Taxcode in (select TaxCode from msalestax where ReversionNo=" + strReversionNo + " and Provno='" + strProvNo + "' and CountyNo='" + strCountyNo + "')";

                    RefreshTmpDtl(dBAccess, strCityNo, 3, strCon, strTaxCode);
                    if (bTaxLevel3NeedDtl)
                        strSql = "select isnull(sum(TaxPct),0) as taxpct from #msalesTaxDtl where TaxLevel=3";
                    else
                        strSql = "select CityTaxPct as TaxPct from msalesTax where ProvNo='" + strProvNo + "' and CountyNo='" + strCountyNo + "' and  CityNo='" + strCityNo + "' and ReversionNo=" + strReversionNo;

                    break;
                case 4:
                    
                    if (bTaxLevel1NeedDtl)
                        strCon = " Taxcode in (select TaxCode from msalestaxdtl where ReversionNo=" + strReversionNo + " and CodeTaxLevel='" + strProvNo + "' and TaxLevel=1)";
                    else
                        strCon = " Taxcode in (select TaxCode from msalestax where ReversionNo=" + strReversionNo + " and Provno='" + strProvNo + "')";
                    
                    if (bTaxLevel2NeedDtl)
                        strCon+= " and Taxcode in (select TaxCode from msalestaxdtl where ReversionNo=" + strReversionNo + " and CodeTaxLevel='" + strCountyNo + "' and TaxLevel=2)";
                    else
                        strCon+= " and Taxcode in (select TaxCode from msalestax where ReversionNo=" + strReversionNo + " and Provno='" + strProvNo + "' and CountyNo='" + strCountyNo + "')";
                    
                    if (bTaxLevel3NeedDtl)
                        strCon+= " and Taxcode in (select TaxCode from msalestaxdtl where ReversionNo=" + strReversionNo + " and CodeTaxLevel='" + strCityNo + "' and TaxLevel=3)";
                    else
                        strCon+= " and Taxcode in (select TaxCode from msalestax where ReversionNo=" + strReversionNo + " and Provno='" + strProvNo + "' and CountyNo='" + strCountyNo + "' and CityNo='" + strCityNo + "')";
                    
                    RefreshTmpDtl(dBAccess, strTaxLevel4No, 4, strCon, strTaxCode);
                    if (bTaxLevel4NeedDtl)
                        strSql = "select isnull(sum(TaxPct),0) as taxpct from #msalesTaxDtl where TaxLevel=1";
                    else
                        strSql = "select PctforTaxLevel4 as TaxPct from msalesTax where ProvNo='" + strProvNo + "' and CountyNo='" + strCountyNo + "' and  CityNo='" + strCityNo + "' and CodeforTaxLevel4='" + strTaxLevel4No + "' and ReversionNo=" + strReversionNo;

                    break;
                case 5:
                    
                    if (bTaxLevel1NeedDtl)
                        strCon = " Taxcode in (select TaxCode from msalestaxdtl where ReversionNo=" + strReversionNo + " and CodeTaxLevel='" + strProvNo + "' and TaxLevel=1)";
                    else
                        strCon = " Taxcode in (select TaxCode from msalestax where ReversionNo=" + strReversionNo + " and Provno='" + strProvNo + "')";
                    
                    if (bTaxLevel2NeedDtl)
                        strCon+= " and Taxcode in (select TaxCode from msalestaxdtl where ReversionNo=" + strReversionNo + " and CodeTaxLevel='" + strCountyNo + "' and TaxLevel=2)";
                    else
                        strCon+= " and Taxcode in (select TaxCode from msalestax where ReversionNo=" + strReversionNo + " and Provno='" + strProvNo + "' and CountyNo='" + strCountyNo + "')";
                    
                    if (bTaxLevel3NeedDtl)
                        strCon+= " and Taxcode in (select TaxCode from msalestaxdtl where ReversionNo=" + strReversionNo + " and CodeTaxLevel='" + strCityNo + "' and TaxLevel=3)";
                    else
                        strCon+= " and Taxcode in (select TaxCode from msalestax where ReversionNo=" + strReversionNo + " and Provno='" + strProvNo + "' and CountyNo='" + strCountyNo + "' and CityNo='" + strCityNo + "')";
                    
                    if (bTaxLevel4NeedDtl)
                        strCon+= " and Taxcode in (select TaxCode from msalestaxdtl where ReversionNo=" + strReversionNo + " and CodeTaxLevel='" + strTaxLevel4No + "' and TaxLevel=4)";
                    else
                        strCon+= " and Taxcode in (select TaxCode from msalestax where ReversionNo=" + strReversionNo + " and Provno='" + strProvNo + "' and CountyNo='" + strCountyNo + "' and CityNo='" + strCityNo + "' and CodeforTaxLevel4='" + strTaxLevel4No + "')";

                    RefreshTmpDtl(dBAccess, strTaxLevel5No, 5, strCon, strTaxCode);
                    if (bTaxLevel5NeedDtl)
                        strSql = "select isnull(sum(TaxPct),0) as taxpct from #msalesTaxDtl where TaxLevel=1";
                    else
                        strSql = "select PctforTaxLevel5 as TaxPct from msalesTax where ProvNo='" + strProvNo + "' and CountyNo='" + strCountyNo + "' and  CityNo='" + strCityNo + "' and CodeforTaxLevel4='" + strTaxLevel4No + "' and CodeforTaxLevel5='" + strTaxLevel5No + "' and ReversionNo=" + strReversionNo;

                    break;
                case 6:
                    break;
            }

            drTaxDtl = dBAccess.GetDataFirstRow(strSql);
            if (drTaxDtl != null) strPct = BaseFunctions.FormatNumeric(drTaxDtl["TaxPct"].ToString(), "n.4");
            return strPct;

        }

        private void RefreshTmpDtl(DBAccess dBAccess, string strCode, int iLevel, string strCon, string strTaxCode)
        {
            string strSql = string.Empty;
            DataTable dtTaxDtl = null;

            strSql = "select * from #mSalesTaxdtl where CodeTaxLevel='" + strCode + "' and TaxLevel=" + iLevel;
            dtTaxDtl = dBAccess.GetDataTable(strSql);
            //With rsTaxDtl
            if (dtTaxDtl.Rows.Count == 0)
            {                                
                strSql = "insert into #mSalestaxDtl (ReversionNo,TaxCode,CodeTaxLevel,SubItem,TaxPct,TaxLevel) " +
                          " select distinct ReversionNo,'',CodeTaxLevel,SubItem,TaxPct,TaxLevel from mSalestaxDtl where " + 
                           strCon +" and CodeTaxLevel='" + strCode + "' and TaxLevel=" + iLevel;
                dBAccess.ExecuteSql(strSql);

                if (strTaxCode.Length > 10) { strTaxCode = strTaxCode.Substring(0, 10); }
                strSql = "Update #msalesTaxDtl set TaxCode='" + strTaxCode + "'";
                dBAccess.ExecuteSql(strSql);
            }
        }

        public void RefDtlbyLookUp(DBAccess dBAccess, string strRecID, string strReversionNo, string strTaxCode, string strCodeTaxLevel, int intTaxLevel)
        {

            string strSql = string.Empty;
            string strCon = string.Empty;
            string strOldTaxCode = string.Empty;

            if (CvtFunc.ToInt(BaseFunctions.FormatNumeric(strRecID, "n.n")) <= 0) return;

            strCon = "ReversionNo=" + strReversionNo + " and TaxLevel=" + intTaxLevel;
            strSql = "delete from #mSalestaxDtl where " + strCon;
            dBAccess.ExecuteSql(strSql);
            strSql = "Insert into #mSalestaxDtl(ReversionNo,TaxCode,CodeTaxLevel,SubItem,TaxPct,TaxLevel) "+ 
                    "select " + BaseFunctions.FormatNumeric(strReversionNo, "n.0") + ",'" + strTaxCode + "','" +
                    strCodeTaxLevel + "',a.SubItem,a.TaxPct,a.TaxLevel from msalestaxDtl a inner join msalestax b on " +
                    "a.TaxCode=b.TaxCode and a.ReversionNo=b.ReversionNo and b.Recid=" + strRecID + " and a.TaxLevel=" + intTaxLevel;
            dBAccess.ExecuteSql(strSql);
           
        }

        public bool UpdateDtl(DBAccess dBAccess, DataTable dtDtl, string strReversionNo, int iTaxLevel, 
                        string strTaxCode, string[] strArrCode, bool bUpdateTmpTable)
        {
            bool bUpdateDtl = false;
            string strSql = string.Empty;
            string strCodeTaxLevel = string.Empty;
            string strCon = string.Empty;
            string strTableName = string.Empty;
            //int iTaxLevel = 0;
            DataTable dtTable = null;
            DataRow drTable = null;
            DataRow drDtl = null;

            if (bUpdateTmpTable)
                strTableName = "#mSalesTaxDtl";
            else
                strTableName = "mSalesTaxDtl";

            if (iTaxLevel <= 0) return bUpdateDtl;
            switch (iTaxLevel)
            {
                case 1:
                    strCodeTaxLevel = strArrCode[0];
                    //iTaxLevel = 1;
                    break;

                case 2:
                    strCodeTaxLevel = strArrCode[1];
                    //iTaxLevel = 2;
                    break;

                case 3:
                    strCodeTaxLevel = strArrCode[2];
                    //iTaxLevel = 3;
                    break;

                case 4:
                    strCodeTaxLevel = strArrCode[3];
                    //iTaxLevel = 4;
                    break;

                case 5:
                    strCodeTaxLevel = strArrCode[4];
                    //iTaxLevel = 5;
                    break;

                case 6:
                    strCodeTaxLevel = strArrCode[5];
                    //iTaxLevel = 6;
                    break;
            }

            strCon = "ReversionNo=" + strReversionNo + " and TaxLevel=" + iTaxLevel;
            strSql = "Delete FROM " + strTableName + " WHERE " + strCon;
            dBAccess.ExecuteSql(strSql);

            strSql = "SELECT RecID, ReversionNo, TaxCode, CodeTaxLevel, SubItem, TaxPct,TaxLevel FROM " + strTableName + " WHERE " + strCon;
            dtTable = dBAccess.GetDataTable(strSql);
            // With rsTable
            for (int i = 0; i< dtDtl.Rows.Count; i++)
            {
                drDtl = dtDtl.Rows[i];
                if (drDtl["SubItem"].ToString().Trim() != string.Empty)
                {
                    drTable = dtTable.NewRow();
                    drTable["ReversionNo"] = BaseFunctions.FormatNumeric(strReversionNo, "n.0");
                    drTable["TaxCode"] = strTaxCode;
                    drTable["CodeTaxLevel"] = strCodeTaxLevel;
                    drTable["SubItem"] = drDtl["SubItem"].ToString();
                    drTable["TaxPct"] = BaseFunctions.FormatNumeric(drDtl["TaxPct"].ToString(), "n.4");
                    drTable["TaxLevel"] = iTaxLevel;
                    DataFunctions.UpdateTable(dBAccess, "ReversionNo,TaxCode,CodeTaxLevel,SubItem,TaxPct,TaxLevel", strTableName, drTable, FormStatus.AddNew, "RecID", false);
                }
            }

            strSql = "update #msalesTaxDtl set TaxCode='" + strTaxCode + "',ReversionNo=" + BaseFunctions.FormatNumeric(strReversionNo, "n.0");
            dBAccess.ExecuteSql(strSql);

            bUpdateDtl = true;
            return bUpdateDtl;
        }

        public string GetTaxDtlSql(DBAccess dBAccess, string strSubItem, string strReversionNo, 
                                int intTaxLevel, string strTaxCode, string[] strArrCode)
        {
            string strSql = string.Empty;
            string strCon = string.Empty;
            string strTableName = string.Empty;
            string strCodeTaxLevel = string.Empty;

            if (strArrCode.Length + 1 <= intTaxLevel) return strSql;
            switch (intTaxLevel)
            {
                case 1:
                    strCodeTaxLevel = strArrCode[0];
                    strCon = "taxcode like '" + strArrCode[0].TrimEnd() + "%'";
                    break;

                case 2:
                    strCodeTaxLevel = strArrCode[1];
                    strCon = "taxcode like '" + strArrCode[0].TrimEnd() + strArrCode[1] + "%'";
                    break;

                case 3:
                    strCodeTaxLevel = strArrCode[2];
                    strCon = "taxcode like '" + strArrCode[0].TrimEnd() + strArrCode[1] + strArrCode[2] + "%'";
                    break;

                case 4:
                    strCodeTaxLevel = strArrCode[3];
                    strCon = "taxcode like '" + strArrCode[0].TrimEnd() + "%'";
                    break;

                case 5:
                    strCodeTaxLevel = strArrCode[4];
                    strCon = "taxcode like '" + strArrCode[0].TrimEnd() + "%'";
                    break;

                case 6:
                    strCodeTaxLevel = strArrCode[5];
                    strCon = "taxcode like '" + strArrCode[0].TrimEnd() + "%'";
                    break;

                default:
                    return strSql;
            }
            strTableName = "mSalesTaxDtl";

            strCon+= " and ReversionNo=" + strReversionNo + " and TaxLevel=" + intTaxLevel;
            strCon+= " and taxcode<>'" + strTaxCode + "'";
            if (strSubItem != string.Empty)
                strCon+= " and SubItem ='" + strSubItem + "'";

            strSql = "SELECT ReversionNo, TaxCode, CodeTaxLevel, SubItem, TaxPct,TaxLevel FROM " + strTableName + " WHERE " + strCon;

            return strSql;
        }
        #endregion
    }
}